﻿Imports System.Text
Imports System.IO
Imports System
Imports System.Xml
Imports System.Security.Cryptography
Imports System.Text.RegularExpressions
Imports System.Windows.Controls
Imports CommonDialog

Public Class CommonFuncs
    Private Shared key() As Byte = {}
    Private Shared IV() As Byte = {&H12, &H34, &H56, &H78, &H90, &HAB, &HCD, &HEF}
    Public Declare Function FindExecutable Lib "shell32" _
   Alias "FindExecutableA" _
  (ByVal lpFile As String, _
   ByVal lpDirectory As String, _
   ByVal sResult As String) As Long
    Public Enum DBIdTypes
        CompanyID = 1
        PersonID = 2
        CaseID = 3
        InvoiceID = 4
    End Enum
    Public Shared Function GetFileName(ByVal flname As String) As String

        'Get the filename without the path or extension.
        'Input Values:
        '   flname - path and filename of file.
        'Return Value:
        '   GetFileName - name of file without the extension.

        Dim posn As Integer, i As Integer
        Dim fName As String

        posn = 0
        'find the position of the last "\" character in filename
        For i = 1 To Len(flname)
            If (Mid(flname, i, 1) = "\") Then posn = i
        Next i

        'get filename without path
        fName = Right(flname, Len(flname) - posn)

        'get filename without extension
        'posn = InStr(fName, ".")
        'If posn <> 0 Then
        'fName = Left(fName, posn - 1)
        'End If
        GetFileName = fName
    End Function

    Public Function SwapQuotes(ByVal AnyString As String) As String
        Try
            If Not IsNothing(AnyString) Then
                Return AnyString.Replace("'", "''")
            Else
                Return String.Empty
            End If
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function
    Public Sub ForceDeleteTempDir()
        If Directory.Exists(System.IO.Path.GetTempPath & "CRMTemp") Then
            Dim j As Integer
            Dim FileNames() As String = Directory.GetFiles(System.IO.Path.GetTempPath & "CRMTemp")
            For j = 0 To FileNames.Length - 1
                '                        System.IO.File.Delete(FileNames(j))
                Dim fso As Object
                fso = CreateObject("Scripting.FileSystemObject")

                fso.DeleteFile(FileNames(j), True)
            Next
            Directory.Delete(System.IO.Path.GetTempPath & "CRMTemp")
        End If
    End Sub
    Public Function GetValue(ByVal objDR As DataRow, ByVal strField As String, ByVal defaultvalue As Object)
        Try

        If objDR(strField) Is DBNull.Value Then
                Return defaultvalue
            Else
                Return objDR(strField)
            End If
        Catch ex As Exception
            Return defaultvalue
        End Try

    End Function
   
    Public Function SaveEmailAsDraft(ByVal FileName As String, ByVal Content As String) As Boolean

        Dim sw As IO.StreamWriter
        sw = New IO.StreamWriter(FileName)
        Try
            sw.Write(Content.ToString)
            Return True
        Catch es As Exception
            'ErrorHandler(es, "CommonFunc.SaveEmailAsDraft", True)
            'MsgBox(es.Message)
            Return False
        Finally
            If Not (sw Is Nothing) Then
                sw.Close()
            End If
        End Try
    End Function
    Public Function FormatDate(ByVal AnyDate As Date, Optional ByVal FormatTime As Boolean = False) As String

        'Main purpose of function is to stop 00:00 being displayed when dates are null
        Try
            If AnyDate.Year < 1800 Then
                Return String.Empty
            Else
                If Not FormatTime Then
                    Return Format(AnyDate, "dd-MMM-yyyy")
                Else
                    Return Format(AnyDate, "dd-MMM-yyyy HH:mm:ss")
                End If
            End If
        Catch ex As Exception
            Return String.Empty
        End Try
    End Function
    Public Function AddWorkingDays(ByVal DateIn As DateTime, ByVal ShiftDate As Integer) As DateTime
        Try
            ' Adds the [ShiftDate] number of working days to DateIn
            Dim datDate As DateTime = DateIn.AddDays(ShiftDate)
            ' Loop around until we get the need non-weekend day
            While Weekday(datDate) = 1 Or Weekday(datDate) = 7
                datDate = datDate.AddDays(IIf(ShiftDate < 0, -1, 1))
            End While
            Return datDate
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
    Public Shared Function Decryption(ByVal stringToDecrypt As String) As String
        Dim sEncryptionKey As String = "~!@#$)()(%$#@!()~^$%()&^$%@#!~"

        'sEncryptionKey = "&%#@?,:*"
        Dim inputByteArray(stringToDecrypt.Length) As Byte
        Try
            key = System.Text.Encoding.UTF8.GetBytes(Left(sEncryptionKey, 8))
            Dim des As New DESCryptoServiceProvider()
            inputByteArray = Convert.FromBase64String(stringToDecrypt)
            Dim ms As New MemoryStream()
            Dim cs As New CryptoStream(ms, des.CreateDecryptor(key, IV), _
                CryptoStreamMode.Write)
            cs.Write(inputByteArray, 0, inputByteArray.Length)
            cs.FlushFinalBlock()
            Dim encoding As System.Text.Encoding = System.Text.Encoding.UTF8
            Return encoding.GetString(ms.ToArray())
        Catch e As Exception
            Return e.Message
        End Try
    End Function

    Public Shared Function Encryption(ByVal stringToEncrypt As String) As String
        Dim SEncryptionKey As String
        SEncryptionKey = "~!@#$)()(%$#@!()~^$%()&^$%@#!~"
        'sEncryptionKey = "&%#@?,:*"
        'SEncryptionKey = "&%#@?,:*"
        Try
            key = System.Text.Encoding.UTF8.GetBytes(Left(SEncryptionKey, 8))
            Dim des As New DESCryptoServiceProvider()
            Dim inputByteArray() As Byte = Encoding.UTF8.GetBytes( _
                stringToEncrypt)
            Dim ms As New MemoryStream()
            Dim cs As New CryptoStream(ms, des.CreateEncryptor(key, IV), _
                CryptoStreamMode.Write)
            cs.Write(inputByteArray, 0, inputByteArray.Length)
            cs.FlushFinalBlock()
            Return Convert.ToBase64String(ms.ToArray())
        Catch e As Exception
            Return e.Message
        End Try
    End Function
    Public Shared Function ArrayToCSV(ByVal AnyArray() As Long) As String

        Dim i As Integer
        Dim myCSV As New StringBuilder

        myCSV.Append("(")

        For i = AnyArray.GetLowerBound(0) To AnyArray.GetUpperBound(0)
            myCSV.Append(AnyArray(i).ToString)

            If i < AnyArray.GetUpperBound(0) Then
                myCSV.Append(",")
            End If
        Next

        myCSV.Append(")")

        Return myCSV.ToString


    End Function
    Public Shared Function DisplayMessage(ByVal Message As String, ByVal Type As CustomDialogIcons, ByVal Title As String) As Long
        ' MsgBox("Error occurred in " & strFormAndFunction & vbCrLf & vbCrLf & exc.Message)
        Try
            Dim obj As New CommonDialog.CustomDialog
            obj.AdditionalDetailsText = "Please contact IT for more information"
            obj.Buttons = CommonDialog.CustomDialogButtons.OK
            obj.Caption = Title
            obj.DefaultButton = CommonDialog.CustomDialogResults.OK
            obj.FooterIcon = CommonDialog.CustomDialogIcons.Shield
            obj.FooterText = "IT Message - CRM"
            If GlobalCRM.CurrentCaseID > 0 Then
                obj.InstructionHeading = "Case id : " & GlobalCRM.CurrentCaseID
            End If
            obj.InstructionIcon = Type
            obj.InstructionText = Message
            'Dim objResults As CommonDialog.CustomDialogResults = obj.Show
            obj.Show()
            Return 1
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Shared Function DisplayMessageWithoutCaseId(ByVal Message As String, ByVal Type As CustomDialogIcons, ByVal Title As String) As Long
        ' MsgBox("Error occurred in " & strFormAndFunction & vbCrLf & vbCrLf & exc.Message)
        Try
            Dim obj As New CommonDialog.CustomDialog
            obj.AdditionalDetailsText = "Please contact IT for more information"
            obj.Buttons = CommonDialog.CustomDialogButtons.OK
            obj.Caption = Title
            obj.DefaultButton = CommonDialog.CustomDialogResults.OK
            obj.FooterIcon = CommonDialog.CustomDialogIcons.Shield
            obj.FooterText = "IT Message - CRM"
            obj.InstructionIcon = Type
            obj.InstructionText = Message
            'Dim objResults As CommonDialog.CustomDialogResults = obj.Show
            obj.Show()
            Return 1
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Shared Function DisplayyesNoMessage(ByVal Message As String, ByVal Type As CustomDialogIcons, ByVal Title As String) As Long
        ' MsgBox("Error occurred in " & strFormAndFunction & vbCrLf & vbCrLf & exc.Message)
        Try

            ' MsgBox("Error occurred in " & strFormAndFunction & vbCrLf & vbCrLf & exc.Message)

            Dim obj As New CommonDialog.CustomDialog
            obj.AdditionalDetailsText = "Please contact IT for more information"
            obj.Buttons = CommonDialog.CustomDialogButtons.YesNo
            obj.Caption = "CRM"
            obj.DefaultButton = CommonDialog.CustomDialogResults.Yes
            obj.FooterIcon = CommonDialog.CustomDialogIcons.Shield
            obj.FooterText = "IT System"
            obj.InstructionHeading = "IT Message"
            obj.InstructionIcon = CommonDialog.CustomDialogIcons.Warning
            obj.InstructionText = Message
            Dim objResults As CommonDialog.CustomDialogResults = obj.Show
            If objResults = CustomDialogResults.Yes Then

                Return 1
            Else : Return 0
            End If


        Catch ex As Exception
            Return 0
        End Try
    End Function
    Public Shared Function ErrorHandler(ByVal exc As Exception, ByVal strFormAndFunction As String, Optional ByVal bHideDisplay As Boolean = False, Optional ByVal Priority As IT.IncidentPriority = IT.IncidentPriority.Medium) As Long

        Dim SQL As New StringBuilder
        Dim strCon As String = CommonDataFunctions.GetConsultantNameNT
        Dim Db As New Global_Visas_Data_Layer.DataAccess()
        Try
            With SQL
                .Append("Insert into D_Errors ( Error_CaseID, Error_Function, Error_TimeDate, Error_Consultant, Error_Message ) Values ( ")
                .Append(CStr(GlobalCRM.CurrentCaseID) & ", ")
                .Append("'" & strFormAndFunction & "',")
                .Append("getdate(),")
                ' .Append("'" & FormatDay(Now, True) & "',")
                .Append("'" & strCon & "',")
                .Append("'" & Replace(exc.Message, "'", ".") & "')")
            End With
            Db.ExecuteSQL(SQL.ToString)
            If bHideDisplay = False Then
                ' MsgBox("Error occurred in " & strFormAndFunction & vbCrLf & vbCrLf & exc.Message)
                Dim incidentNo As Long
                Dim obj As New CommonDialog.CustomDialog
                obj.AdditionalDetailsText = exc.Message & vbCrLf & vbCrLf & "Module : " & strFormAndFunction
                obj.Buttons = CommonDialog.CustomDialogButtons.YesNo
                obj.Caption = "CRM V3"
                obj.DefaultButton = CommonDialog.CustomDialogResults.Yes
                obj.FooterIcon = CommonDialog.CustomDialogIcons.Shield
                obj.FooterText = "IT System - CRM Error"
                obj.InstructionHeading = "IT Error"
                obj.InstructionIcon = CommonDialog.CustomDialogIcons.Warning
                obj.InstructionText = "Click yes to report this error to IT."
                Dim objResults As CommonDialog.CustomDialogResults = obj.Show
                If objResults = CustomDialogResults.Yes Then
                    incidentNo = IT.CreateNewIncident(GlobalCRM.CurrentConID, GlobalCRM.CurrentConName, strFormAndFunction, exc.Message, exc.ToString, Priority)
                    'Dim frm As New frmReportForm
                    'Dim frmReportForm As New frmReportForm
                    'frmReportForm
                    Return incidentNo
                End If
                'tbResults.Text = String.Format("Last dialog result was {0}", objResults.ToString)

            End If
        Catch ex As Exception
            ' Do Nothing
            Return 0
        Finally
            Db = Nothing
        End Try
    End Function

    'Public Shared Function GetPopUpCalendarDate(ByVal frmCalendarPopUp As Windows.Window, Optional ByVal strCurrentDate As String = string.empty) As String
    '    ' get date selected in calendar pop up (or return their current date if they click Cancel)

    '    frmCalendarPopUp.ShowDialog()

    '    If Format(GlobalCRM.DatePopUpDate, "dd-MMM-yyyy") = "01-Jan-0001" Then
    '        GetPopUpCalendarDate = strCurrentDate
    '    Else
    '        GetPopUpCalendarDate = Format(GlobalCRM.DatePopUpDate, "dd-MMM-yyyy")
    '    End If

    'End Function
    Public Shared Function GetPopUpCalendarDate(Optional ByVal strCurrentDate As String = "") As String
        ' get date selected in calendar pop up (or return their current date if they click Cancel)

        ' frmCalendarPopUp.ShowDialog()

        If Format(GlobalCRM.DatePopUpDate, "dd-MMM-yyyy") = "01-Jan-0001" Then
            GetPopUpCalendarDate = strCurrentDate
        Else
            GetPopUpCalendarDate = Format(GlobalCRM.DatePopUpDate, "dd-MMM-yyyy")
        End If

    End Function
  

    Public Function GetAge(ByVal Birthdate As System.DateTime, Optional ByVal AsOf As System.DateTime = #1/1/1700#) As String

        Dim iMonths As Integer
        Dim iYears As Integer
        Dim dYears As Decimal
        Dim lDayOfBirth As Long
        Dim lAsOf As Long
        Dim iBirthMonth As Integer
        Dim iAsOFMonth As Integer

        If AsOf = "#1/1/1700#" Then
            'AsOf = DateTime.Now
            AsOf = CommonDataFunctions.GetLocalServer
        End If
        lDayOfBirth = DatePart(DateInterval.Day, Birthdate)
        lAsOf = DatePart(DateInterval.Day, AsOf)

        iBirthMonth = DatePart(DateInterval.Month, Birthdate)
        iAsOFMonth = DatePart(DateInterval.Month, AsOf)

        iMonths = DateDiff(DateInterval.Month, Birthdate, AsOf)

        dYears = iMonths / 12

        iYears = Math.Floor(dYears)

        If iBirthMonth = iAsOFMonth Then
            If lAsOf < lDayOfBirth Then
                iYears = iYears - 1
            End If
        End If

        Return iYears
    End Function
    Public Shared Function DateDiffW(ByVal StDate As Date, ByVal EnDate As Date) As Integer



        Dim IntInterval As Integer, DTCurrent As Date, DTStart As Date, DTEnd As Date

        DTStart = (New CommonFuncs()).FormatDate(StDate) 'Format(StDate, "dd/mm/yyyy")
        DTEnd = (New CommonFuncs()).FormatDate(EnDate) 'Format(EnDate, "dd/mm/yyyy")

        If DateDiff("d", DTStart, DTEnd) > 30000 Then
            DateDiffW = 0
            Exit Function
        End If

        If DTStart > DTEnd Then
            DateDiffW = 0
            Exit Function
        End If

        IntInterval = DateDiff("d", DTStart, DTEnd)
        DTCurrent = (New CommonFuncs()).FormatDate(DateAddW(DTStart, IntInterval)) ', "dd/mm/yyyy")

        Do While DTCurrent <> DTEnd And IntInterval > 0

            IntInterval = IntInterval - 1
            DTCurrent = DateAddW(DTStart, IntInterval)

        Loop

        DateDiffW = IntInterval



    End Function
    Private Shared Function DateAddW(ByVal Startdate As Date, ByVal Interval As Integer) As Date


        Dim Weeks As Long, OddDays As Long, Temp As String

        If VarType(Startdate) <> 7 Or VarType(Interval) < 2 Or VarType(Interval) > 5 Then
            DateAddW = Startdate
        ElseIf Interval = 0 Then
            DateAddW = Startdate
        ElseIf Interval > 0 Then
            Interval = Int(Interval)

            ' Make sure TheDate is a workday (round down).
            Temp = Format(Startdate, "ddd")
            If Temp = "Sun" Then
                Startdate.AddDays(-2)
            ElseIf Temp = "Sat" Then
                Startdate.AddDays(-1)
            End If

            ' Calculate Weeks and OddDays.
            Weeks = Int(Interval / 5)
            OddDays = Interval - (Weeks * 5)
            Startdate.AddDays(Weeks * 7)

            ' Take OddDays weekend into account.
            If (DatePart("w", Startdate) + OddDays) > 6 Then
                Startdate.AddDays(OddDays + 2)
            Else
                Startdate.AddDays(OddDays)
            End If

            DateAddW = Startdate
        Else                         ' Interval is < 0
            Interval = Int(-Interval) ' Make positive & subtract later.

            ' Make sure startdate is a workday (round up).
            Temp = Format(Startdate, "ddd")
            If Temp = "Sun" Then
                Startdate.AddDays(1)
            ElseIf Temp = "Sat" Then
                Startdate.AddDays(2)
            End If

            ' Calculate Weeks and OddDays.
            Weeks = Int(Interval / 5)
            OddDays = Interval - (Weeks * 5)
            Startdate.AddDays(Weeks * 7)

            ' Take OddDays weekend into account.
            If (DatePart("w", Startdate) - OddDays) < 2 Then
                Startdate.AddDays(-OddDays - 2)
            Else
                Startdate.AddDays(-OddDays)
            End If

            DateAddW = Startdate
        End If

    End Function

    Public Shared Function WriteOffAccess(ByVal Con_id As Long) As Long
        Dim sql As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        sql.Append("select con_writeoff from l_consultant where con_id = " & Con_id)
        Dim sData As Object = DB.GetSQLScalar(sql.ToString)
        If sData.GetType.ToString = "System.DBNull" Then
            Return 0
        Else
            Return CLng(sData)
        End If

    End Function

    Public Shared Function RevertWriteOffAccess(ByVal Con_id As Long) As Long
        Dim sql As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        sql.Append("select con_revertwriteoff from l_consultant where con_id = " & Con_id)
        Dim sData As Object = DB.GetSQLScalar(sql.ToString)
        If sData.GetType.ToString = "System.DBNull" Then
            Return 0
        Else
            Return CLng(sData)
        End If

    End Function

    Public Shared Function ISWithCreditController(ByVal caseid As Long) As Boolean
        Dim sql As StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        sql = New StringBuilder
        Dim IsCreditCont As Object
        sql.Append("select con_iscreditcontroller from l_consultant where con_id = " & GlobalCRM.CurrentConID)
        IsCreditCont = DB.GetSQLScalar(sql.ToString)
        If IsCreditCont.GetType.ToString = "System.DBNull" Then
            'check whether the case is with the person who has logged in. If the person with whom the case is present is a cc then display
            'message that the case is with CC
            sql = New StringBuilder
            sql.Append("Select case_conCWid from d_case where case_id = " & caseid)
            Dim Consaleid As Object = DB.GetSQLScalar(sql.ToString)
            If Consaleid.GetType.ToString = "System.DBNull" Then
            Else
                If CLng(Consaleid) = GlobalCRM.CurrentConID Then
                    Return False
                Else
                    sql = New StringBuilder
                    sql.Append("select con_iscreditcontroller from l_consultant where con_id = " & CLng(Consaleid))
                    IsCreditCont = DB.GetSQLScalar(sql.ToString)
                    If Not IsCreditCont Is Nothing Then
                        If IsCreditCont.GetType.ToString = "System.DBNull" Then
                            Return False
                        End If
                        If CLng(IsCreditCont) >= 1 Then
                            Return True
                        End If
                    End If
                End If
            End If
        Else
            If CLng(IsCreditCont) >= 1 Then
                Return False
            End If
        End If

    End Function

    Public Shared Function ISCaseWrittenOff(ByVal caseid As Long) As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Dim sql As StringBuilder
        sql = New StringBuilder
        Dim writeoff As Object
        With sql
            .Append("select writeoff from d_case where case_id = " & caseid)
        End With
        writeoff = DB.GetSQLScalar(sql.ToString)
        If Not writeoff Is Nothing Then
            If writeoff.GetType.ToString = "System.DBNull" Then
                Return False
            Else
                If CLng(writeoff) = 1 Then
                    Return True
                End If
                If CLng(writeoff) = 0 Then
                    Return False
                End If
            End If
        Else
            Return False
        End If
    End Function

    'Public Shared Function GblFunc_Goto_ID(ByVal DataID As Long, ByVal DataType As DBIdTypes) As Integer
    '    ' used immediately prior to opening the frmMainDisplay form, to log user movement around CRM
    '    ' by logging an event in the D_Event table and logging a history in the S_UserHistory table

    '    Dim EventMessage As New StringBuilder
    '    Dim HistoryMessage As New StringBuilder
    '    Dim DB As New Global_Visas_Data_Layer.DataAccess()
    '    'Check to see if provided record ID exists in the relevent table.
    '    Select Case DataType

    '        Case DBIdTypes.CompanyID

    '            EventMessage.Append("Opened company " & GlobalCRM.CurrentCompanyName & " (" & GlobalCRM.CurrentCompanyID & ")")
    '            HistoryMessage.Append(GlobalCRM.CurrentCompanyName)


    '        Case DBIdTypes.PersonID

    '            EventMessage.Append("Opened Person " & GlobalCRM.CurrentCandidateName & " (" & GlobalCRM.CurrentCandidateID & ")")
    '            HistoryMessage.Append(GlobalCRM.CurrentCandidateName)

    '            If GlobalCRM.CurrentCompanyID <> 0 Then
    '                EventMessage.Append(" of company " & GlobalCRM.CurrentCompanyName & " (" & GlobalCRM.CurrentCompanyID & ")")
    '                HistoryMessage.Append(" (" & GlobalCRM.CurrentCompanyName & ")")
    '            Else
    '                EventMessage.Append(", private individual")
    '            End If


    '        Case DBIdTypes.CaseID

    '            Dim SQL As New StringBuilder

    '            EventMessage.Append("Opened case number " & GlobalCRM.CurrentCaseID)

    '            With SQL
    '                .Append("SELECT ISNULL(L_Products.Product_Name, 'Unspecified') AS Product")
    '                .Append(" FROM D_Case LEFT OUTER JOIN L_Products ON D_Case.Case_ProductID = L_Products.Product_ID")
    '                .Append(" WHERE D_Case.Case_ID = " & GlobalCRM.CurrentCaseID)
    '            End With


    '            HistoryMessage.Append(DB.GetSQLScalar(SQL.ToString) & " for " & GlobalCRM.CurrentCandidateName)

    '            If GlobalCRM.CurrentCompanyID <> 0 Then
    '                EventMessage.Append(" for company " & GlobalCRM.CurrentCompanyName & " (" & GlobalCRM.CurrentCompanyID & ")")
    '                HistoryMessage.Append("(" & GlobalCRM.CurrentCompanyName & ")")
    '            Else
    '                EventMessage.Append(" for private individual" & GlobalCRM.CurrentCandidateName & " (" & GlobalCRM.CurrentCandidateID & ")")
    '            End If


    '        Case DBIdTypes.InvoiceID

    '            EventMessage.Append("Opened invoice number " & GlobalCRM.CurrentInvoiceId & " for case number " & GlobalCRM.CurrentCaseID)
    '            HistoryMessage.Append("For Case number " & GlobalCRM.CurrentCaseID)

    '            If GlobalCRM.CurrentCompanyID <> 0 Then
    '                EventMessage.Append(" for company " & GlobalCRM.CurrentCompanyName & " (" & GlobalCRM.CurrentCompanyID & ")")
    '                HistoryMessage.Append(GlobalCRM.CurrentCompanyName)
    '            Else
    '                EventMessage.Append(" for private individual" & GlobalCRM.CurrentCandidateName & " (" & GlobalCRM.CurrentCandidateID & ")")
    '                HistoryMessage.Append(" (" & GlobalCRM.CurrentCandidateName & ")")
    '            End If

    '    End Select

    '    ' log event
    '    GblFunc_Add_SysEvent("Activity", EventMessage.ToString)
    '    ' log history
    '    Gblfunc_Add_History(DataID, DataType, HistoryMessage.ToString)

    ''End Function
    'Public Shared Function GblFunc_Add_SysEvent(ByVal EventType As String, ByVal Detail As String) As Boolean

    '    Dim SQL As New StringBuilder
    '    Dim DB As New Global_Visas_Data_Layer.DataAccess()
    '    With SQL

    '        .Append("Insert into S_SysEvents (SysEvent_UserID, SysEvent_Username, SysEvent_Type, SysEvent_Detail, SysEvent_SysVersion)")
    '        .Append(" Values ( ")
    '        .Append(GlobalCRM.CurrentConID & ", ")
    '        .Append("'" & SwapsQuotes(GlobalCRM.CurrentConName) & "',")
    '        .Append("'" & SwapsQuotes(EventType) & "',")
    '        .Append(" '" & SwapsQuotes(Detail) & "',")
    '        '.Append ( " 'V1:" & Gbl_Int_SysVersion & Gbl_Str_SubVersion & "')")
    '        '.Append("'V" & System.Windows.Controls.Application.ProductVersion & "')")
    '        .Append("'V3.0')")
    '    End With

    '    Return DB.ExecuteSQL(SQL.ToString)

    'End Function
    Public Shared Function SwapsQuotes(ByVal AnyString As String) As String

        If Not IsNothing(AnyString) Then
            Return AnyString.Replace("'", "''")
        Else
            Return String.Empty
        End If

    End Function
    Public Shared Function Gblfunc_Add_History(ByVal LinkID As Long, ByVal LinkType As Integer, ByVal Message As String) As Boolean

        Dim SQL As New StringBuilder
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        If LinkID = 0 Or LinkType = 0 Or Message = String.Empty Then
            Exit Function
        End If

        If Message.Length > 100 Then
            Message = Left(Message, 100)
        End If

        With SQL
            .Append("Insert into S_UserHistory")
            .Append(" (UserHistory_LinkType, UserHistory_LinkID, UserHistory_UserID, UserHistory_Text)")
            .Append(" VALUES (" & LinkType & ", " & LinkID & ", " & GlobalCRM.CurrentConID & ", '" & SwapsQuotes(Message) & "')")
        End With

        If DB.ExecuteSQL(SQL.ToString) Then

            Dim myData As DataTable

            'Check if more than 20 and remove excess

            SQL = New StringBuilder

            With SQL
                .Append("Select UserHistory_ID FROM S_Userhistory ")
                .Append("WHERE UserHistory_UserId = " & GlobalCRM.CurrentConID)
                .Append(" Order By Userhistory_dt")
            End With

            myData = DB.GetSQLDataTable(SQL.ToString)

            If Not IsNothing(myData) Then

                If myData.Rows.Count > 20 Then

                    Dim OldestHistoryId As Long
                    Dim TopId As Long
                    Dim BottomId As Long
                    Dim myHistory As DataTable

                    OldestHistoryId = myData.Rows(20).Item("userhistory_id")

                    SQL = New StringBuilder

                    With SQL
                        .Append("select top 20 userhistory_id from s_userhistory  ")
                        .Append("where UserHistory_UserId = " & GlobalCRM.CurrentConID)
                        .Append(" and Userhistory_id < " & OldestHistoryId & " order by userhistory_id")
                    End With

                    myHistory = DB.GetSQLDataTable(SQL.ToString)

                    TopId = myData.Rows(myData.Rows.Count - 21).Item("userhistory_id")
                    BottomId = myData.Rows(0).Item("userhistory_id")

                    myHistory.Dispose()

                    'Delete any history for this consultant with a lower history id
                    SQL = New StringBuilder

                    With SQL
                        .Append("delete from s_userhistory where UserHistory_UserId = " & GlobalCRM.CurrentConID)
                        .Append(" and Userhistory_id between " & BottomId & " and " & TopId)
                    End With

                    DB.ExecuteSQL(SQL.ToString)

                End If

            End If

        End If

    End Function

 
    Public Shared Function Gblfunc_Validate_EmailAddress(ByVal StrAddress As String) As String
        Try

        If Left(StrAddress, 1) = "@" Then
                Gblfunc_Validate_EmailAddress = "Invalid e-mail address, nothing to the left of the @ symbol"
                Exit Function
            End If

            If Right(StrAddress, 1) = "@" Then
                Gblfunc_Validate_EmailAddress = "Invalid e-mail address, nothing to the right of the @ symbol"
                Exit Function
            End If

            If InStr(StrAddress, "@") = 0 Then
                Gblfunc_Validate_EmailAddress = "Invalid e-mail address, no @ symbol found"
                Exit Function
            End If

            If InStr(Right(StrAddress, Len(StrAddress) - InStr(StrAddress, "@")), "@") Then
                Gblfunc_Validate_EmailAddress = "Invalid e-mail address, more than one @ symbol found"
                Exit Function
            End If

            If InStr(StrAddress, ".") = 0 Then
                Gblfunc_Validate_EmailAddress = "Invalid e-mail address, no . symbol found"
                Exit Function
            End If

            If InStr(StrAddress, "..") Then
                Gblfunc_Validate_EmailAddress = "Invalid e-mail address, address cannot have adjacent . symbols"
                Exit Function
            End If

            If InStr(StrAddress, ";") Then
                Gblfunc_Validate_EmailAddress = "Invalid e-mail address, address cannot contain ; symbol"
                Exit Function
            End If

            If InStr(StrAddress, " ") Then
                Gblfunc_Validate_EmailAddress = "Invalid e-mail address, address cannot contain spaces"
                Exit Function
            End If

            If InStr(StrAddress, ",") Then
                Gblfunc_Validate_EmailAddress = "Invalid e-mail address, address cannot contain , character"
                Exit Function
            End If

            Return String.Empty

        Catch ex As Exception
            Return String.Empty
        End Try

    End Function

    Public Shared Sub BeginTrans()
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            DB.BeginTrans()
        Catch ex As Exception
        Finally
            DB = Nothing
        End Try
    End Sub

    Public Shared Sub RollbackTrans()
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            DB.RollbackTrans()
        Catch ex As Exception
        Finally
            DB = Nothing
        End Try
    End Sub


    Public Shared Sub CommitTrans()
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            DB.CommitTrans()
        Catch ex As Exception
        Finally
            DB = Nothing
        End Try
    End Sub

    Public Shared Function IsTransactionOpen() As Boolean
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Try
            If DB.IsTransactionOpen() = True Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            Return False
        Finally
            DB = Nothing
        End Try
    End Function
    Public Shared Function ManipulateDT(ByVal caseids As DataTable) As String
        Dim strCase As String = String.Empty
        Dim i As Integer
        For i = 0 To caseids.Rows.Count - 1
            If strCase = String.Empty Then
                strCase = caseids.Rows(i).Item(0).ToString
            Else
                strCase = strCase & "," & caseids.Rows(i).Item(0).ToString
            End If
        Next
        Return strCase
    End Function

    Public Shared Function DBIdExists(ByVal DBId As Long, ByVal DBIDType As DBIdTypes) As Boolean

        Dim SQL As New StringBuilder
        Dim c As Integer
        Dim DB As New Global_Visas_Data_Layer.DataAccess()
        Select Case DBIDType
            Case DBIdTypes.CaseID
                SQL.Append("select count(*) from d_case where case_id = " & DBId)
            Case DBIdTypes.CompanyID
                SQL.Append("select count(*) from d_company where company_id = " & DBId)
            Case DBIdTypes.PersonID
                SQL.Append("select count(*) from d_person where person_id = " & DBId)
            Case DBIdTypes.InvoiceID
                SQL.Append("select count(*) from a_invoices where invoice_id = " & DBId)

        End Select

        c = DB.GetSQLScalar(SQL.ToString)

        If c > 0 Then
            Return True
        Else
            Return False
        End If

    End Function

    Public Shared Function IDSearch(ByVal lngSearchID As Long, ByVal lngSearchType As Integer) As String
        ' perform ID search on company,person,case or invoice.

        IDSearch = String.Empty

        ' handle Case search
        If lngSearchType = GlobalCRM.enuSearchType.eCase Then

            ' check Case_ID exists
            If CommonFuncs.DBIdExists(lngSearchID, CommonFuncs.DBIdTypes.CaseID) = False Then
                IDSearch = "Invalid Case No."
                Exit Function
            End If

            GlobalCRM.CurrentCaseID = lngSearchID

            CommonDataFunctions.GblFunc_Goto_ID(GlobalCRM.CurrentCaseID, CommonFuncs.DBIdTypes.CaseID)
            IDSearch = "ok"

            ' handle Company search
        ElseIf lngSearchType = GlobalCRM.enuSearchType.eCompany Then

            ' check Company_ID exists
            If CommonFuncs.DBIdExists(lngSearchID, CommonFuncs.DBIdTypes.CompanyID) = False Then
                IDSearch = "Invalid Company No."
                Exit Function
            End If

            GlobalCRM.CurrentSearchType = GlobalCRM.enuSearchType.eCompany
            GlobalCRM.CurrentCaseID = 0
            GlobalCRM.CurrentCompanyID = lngSearchID
            CommonDataFunctions.GblFunc_Goto_ID(GlobalCRM.CurrentCompanyID, CommonFuncs.DBIdTypes.CompanyID)
            IDSearch = "ok"

            ' handle Person search
        ElseIf lngSearchType = GlobalCRM.enuSearchType.ePerson Then

            ' check Person_ID exists
            If CommonFuncs.DBIdExists(lngSearchID, CommonFuncs.DBIdTypes.PersonID) = False Then
                IDSearch = "Invalid Person No."
                Exit Function
            End If

            GlobalCRM.CurrentSearchType = GlobalCRM.enuSearchType.ePerson
            GlobalCRM.CurrentCaseID = 0
            GlobalCRM.CurrentCandidateID = lngSearchID
            CommonDataFunctions.GblFunc_Goto_ID(GlobalCRM.CurrentCandidateID, CommonFuncs.DBIdTypes.PersonID)
            IDSearch = "ok"

            ' handle Invoice search
        ElseIf lngSearchType = GlobalCRM.enuSearchType.eInvoice Then

            ' check Invoice_ID exists
            If CommonFuncs.DBIdExists(lngSearchID, CommonFuncs.DBIdTypes.InvoiceID) = False Then
                IDSearch = "Invalid Invoice No."
                Exit Function
            End If

            GlobalCRM.CurrentCaseID = 0
            GlobalCRM.CurrentInvoiceId = lngSearchID
            GlobalCRM.CurrentCaseID = Cases.GetCaseId(GlobalCRM.CurrentInvoiceId)
            CommonDataFunctions.GblFunc_Goto_ID(GlobalCRM.CurrentInvoiceId, CommonFuncs.DBIdTypes.InvoiceID)
            IDSearch = "ok"

        End If


    End Function
    Public Shared Function isEmailValid(ByVal inputEmail As String) As Boolean

        Dim inputEmail1 As String = inputEmail.ToLower()
        Dim pattern As String = "[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"


        'Dim pattern As String = "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$"
        Dim emailAddressMatch As Match = Regex.Match(inputEmail1, pattern)
        If emailAddressMatch.Success Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Shared Function IsDateValid(ByVal AnyDate As Date) As Boolean

        'Dates such as 12:00, evaluate to being dates, this must be stopped!

        If IsDate(AnyDate) Then
            If AnyDate.Year < 1900 Then
                Return False
            Else
                Return True
            End If
        Else
            Return False
        End If

    End Function
    Public Shared Function FindResigned(ByVal drpControl As ComboBox) As DataTable
        Dim objDR As DataRow
        Dim objDT As DataTable

        Try
            objDT = drpControl.ItemsSource
            For Each objDR In objDT.Rows
                If objDR("con_status") = 0 Then
                    objDR("con_name") = objDR("con_name") & " (R)"
                End If
            Next
            Return objDT
        Catch ex As Exception
            Return Nothing
            ' Do Nothing, field not exist
        End Try
    End Function
    Public Shared Function DropDownId(ByVal AnyDrop As ComboBox) As Long

        If Not IsNothing(AnyDrop.SelectedValue) Then
            If IsNumeric(AnyDrop.SelectedValue) Then
                Return AnyDrop.SelectedValue
            Else
                Return CType(AnyDrop.SelectedValue, DataRowView).Item(0)
            End If
        Else
            Return Nothing
        End If

    End Function
    Public Sub GblFunc_Detect_Acrobat()
        ' check Acrobat installed on users PC
        ' uses Dummy.pdf to check for existance of associated application (ie Acrobat)

        Dim Res As Long
        Dim Pos As Integer
        Dim Result As String = Space$(260)
        Dim Exe As String = String.Empty


        Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader

        Dim DbServerName As String = CType(configurationAppSettings.GetValue("DocStoreServer", GetType(System.String)), String)
        'lpFile: name of the file of interest
        'lpDirectory: location of lpFile
        'sResult: path and name of executable associated with lpFile

        ' Monster is Martin's home computer name
        If Environment.MachineName.ToUpper = "MONSTER" Then
            Res = FindExecutable("Dummy.pdf", "g:\", Result)
        Else
            Res = FindExecutable("Dummy.pdf", "\\" & DbServerName.Trim & "\clients\", Result)
        End If


        Select Case Res

            Case 31
                Exe = "No association"
            Case 2
                Exe = "File not found"
            Case 3
                Exe = "Path not found"
            Case 11
                Exe = "Bad format"

            Case Is >= 32

                Result = Result.Trim

                Pos = InStr(Result, Chr(0))

                If Pos Then
                    Result = Left$(Result, Pos - 1)
                    Exe = IO.Path.GetFileName(Result)
                End If


                Select Case Exe

                    Case "Acrobat.exe"
                        GlobalCRM.Gbl_Int_Acrobat = 2 '(Cannot tell the difference between std and pro, but doesnt matter)

                    Case "AcroRd32.exe"
                        GlobalCRM.Gbl_Int_Acrobat = 1

                End Select

        End Select
    End Sub
    Public Shared Function GetDBConnectionString() As String
        ' get database connection string from the web config file

        Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
        Dim strDatabase As String = CType(configurationAppSettings.GetValue("globalConnString", GetType(System.String)), String)
        If strDatabase.Contains("gvdb.globalvisas.com") Then
            strDatabase = strDatabase & ";Database=global;User Id=GlobalWeb;Password=0cSn3FKzr9;timeout=30;"
        End If
        GetDBConnectionString = strDatabase


    End Function

    Public Shared Function CreateDIR(ByVal sPath As String) As Boolean
        Try

            If Not Directory.Exists(sPath) Then
                'create it if it doesn't
                Directory.CreateDirectory(sPath)
            End If
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Shared Function commandlinedrives(ByVal cmdline As String) As Boolean
        Try

        
            Shell("cmd.exe /c" & cmdline)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function
    Public Shared Sub AddRegValue(ByVal caseid As Long)
        Dim CReg As New CReg
        'Dim value As String = 
        CReg.WriteValue(GlobalCRM.GLOBALVISA, GlobalCRM.CUST, GlobalCRM.Reg_CurrentCASEID, caseid)
        CReg = Nothing
    End Sub
End Class

